﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using StudentManagementSystem.Models;

namespace StudentManagementSystem.Repositories
{
    public class SchoolRepository : BaseRepository
    {
        public SchoolRepository(DB.DBContext dbContext) : base(dbContext) { }

        public int Create(School school)
        {
            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        using (var command = new SQLiteCommand(connection))
                        {
                            command.CommandText = @"
                                INSERT INTO Schools (Name, Address, CreatedAt)
                                VALUES (@Name, @Address, @CreatedAt);
                                SELECT last_insert_rowid();";
                            command.Parameters.AddWithValue("@Name", school.Name);
                            command.Parameters.AddWithValue("@Address", school.Address != null ? (object)school.Address : DBNull.Value);
                            command.Parameters.AddWithValue("@CreatedAt", school.CreatedAt);
                            school.Id = Convert.ToInt32(command.ExecuteScalar());
                        }

                        transaction.Commit();
                        LogAction("CREATE", "Schools", school.Id.ToString(), $"Name: {school.Name}, Address: {school.Address}");
                        return school.Id;
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public School GetById(int id)
        {
            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = @"
                        SELECT * FROM Schools
                        WHERE Id = @Id";
                    command.Parameters.AddWithValue("@Id", id);

                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return new School
                            {
                                Id = reader.GetInt32(reader.GetOrdinal("Id")),
                                Name = reader.GetString(reader.GetOrdinal("Name")),
                                Address = reader.IsDBNull(reader.GetOrdinal("Address")) ? null : reader.GetString(reader.GetOrdinal("Address")),
                                CreatedAt = reader.GetDateTime(reader.GetOrdinal("CreatedAt"))
                            };
                        }
                    }
                }
            }

            return null;
        }

        public List<School> GetAll()
        {
            var schools = new List<School>();

            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "SELECT * FROM Schools ORDER BY Name";

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            schools.Add(new School
                            {
                                Id = reader.GetInt32(reader.GetOrdinal("Id")),
                                Name = reader.GetString(reader.GetOrdinal("Name")),
                                Address = reader.IsDBNull(reader.GetOrdinal("Address")) ? null : reader.GetString(reader.GetOrdinal("Address")),
                                CreatedAt = reader.GetDateTime(reader.GetOrdinal("CreatedAt"))
                            });
                        }
                    }
                }
            }

            return schools;
        }

        public void Update(School school)
        {
            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        using (var command = new SQLiteCommand(connection))
                        {
                            command.CommandText = @"
                                UPDATE Schools
                                SET Name = @Name,
                                    Address = @Address
                                WHERE Id = @Id";
                            command.Parameters.AddWithValue("@Name", school.Name);
                            command.Parameters.AddWithValue("@Address", school.Address != null ? (object)school.Address : DBNull.Value);
                            command.Parameters.AddWithValue("@Id", school.Id);
                            command.ExecuteNonQuery();
                        }

                        transaction.Commit();
                        LogAction("UPDATE", "Schools", school.Id.ToString(), $"Name: {school.Name}, Address: {school.Address}");
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public void Delete(int id)
        {
            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        // 先删除关联的班级
                        using (var classCommand = new SQLiteCommand(connection))
                        {
                            classCommand.CommandText = "DELETE FROM Classes WHERE SchoolId = @SchoolId";
                            classCommand.Parameters.AddWithValue("@SchoolId", id);
                            classCommand.ExecuteNonQuery();
                        }

                        // 再删除学校
                        using (var schoolCommand = new SQLiteCommand(connection))
                        {
                            schoolCommand.CommandText = "DELETE FROM Schools WHERE Id = @Id";
                            schoolCommand.Parameters.AddWithValue("@Id", id);
                            schoolCommand.ExecuteNonQuery();
                        }

                        transaction.Commit();
                        LogAction("DELETE", "Schools", id.ToString());
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
    }
}